﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using 第一个三层架构.Model;
using System.Data.SqlClient;
using System.Data;

namespace 第一个三层架构.DAL
{
    class PersonDAL
    {
        //把要插入数据的模型对象传递给AddNew
        public int AddNew(Person model)
        {
            object obj = SqlHelper.ExecuteScalar(
                "insert into Person(Age,Name) values(@Age,@Name);select @@identity"
                , new SqlParameter("Age", model.Age), new SqlParameter("Name", model.Name));
            //SqlParameter加不加@都一样。
            return Convert.ToInt32(obj);
        }

        public int Delete(int id)
        {
            return SqlHelper.ExecuteNonQuery("delete from Person where id=@id",
                new SqlParameter("id",id));
        }

        public int Update(Person model)
        {
            return SqlHelper.ExecuteNonQuery("update Person set Age=@Age,Name=@Name where id=@id",
                new SqlParameter("id", model.Id), new SqlParameter("Age", model.Age)
                , new SqlParameter("name", model.Name));
        }

        public Person Get(int id)
        {
            DataTable dt = SqlHelper.ExecuteDataTable("select * from Person where id=@id",
                new SqlParameter("id",id));
            if (dt.Rows.Count <= 0)
            {
                return null;
            }
            else if (dt.Rows.Count == 1)
            {
                Person model = new Person();
                DataRow row = dt.Rows[0];
                model.Id = (int)row["id"];
                model.Name = (string)row["Name"];
                model.Age = (int)row["Age"];
                return model;
            }
            else//考虑意外情况
            {
                throw new Exception("出现多条数据！");
            }
        }

        public IEnumerable<Person> GetAll()
        {
            DataTable dt = SqlHelper.ExecuteDataTable("select * from Person");
            List<Person> list = new List<Person>();
            foreach (DataRow row in dt.Rows)
            {
                Person model = new Person();
                model.Id = (int)row["id"];
                model.Name = (string)row["Name"];
                model.Age = (int)row["Age"];
                list.Add(model);
            }
            return list;
        }
    }
}
